import asyncio
import aiomysql
import threading
import time
from setting.mysql_setting import *


# 单例模式
class InsertConnMysql():

    _instance = None
    _initialized = False

    def __new__(cls, *args, **kwargs):#去内存申请地址

        if not cls._instance:
            cls._instance = super().__new__(cls)
        return cls._instance

    def __init__(self, loop=None):#可以传入一个指定的永久loop,

        '''
        参数:loop

        如果实例化的时候自定义了传入了一个loop事件循环,self.newlp=自定义的事件循环,
        如果loop等于none的话，就新建一个事件循环,self.newlp=新建的事件循环,并开一条线程t,并使用thread_running_loop协程函数中的
        set_event_loop(eventloop对象)将eventloop对象指定为当前线程t的eventloop(非主线程的事件循环)
        并把创建协程对象的连接池用create_task添加到的事件循环上去,这时候的协程对象处于已就绪状态。
        '''

        if not self._initialized:
            #创建一个新的事件循环线程
            if loop == None:
                self.newlp = asyncio.new_event_loop()
                
                t = threading.Thread(target=self.thread_running_loop,args=(self.newlp,))     
                t.start()
                
            else:  
                self.newlp = loop
            asyncio.run_coroutine_threadsafe(self.creat_con_pool(),self.newlp)
            print("正在建立mysql线程池")
            time.sleep(1)
            self._initialized = True

    
    def thread_running_loop(self,loop):#开启loop

        '''
        set_event_loop(eventloop对象)将eventloop对象指定为当前线程的eventloop(非主线程的事件循环)
        '''
        asyncio.set_event_loop(loop)    #在此线程中设置一个新的事件循环,默认情况下事件循环是主线程的
        loop.run_forever()


    async def creat_con_pool(self):
        '''
        使用create_pool创建一个连接池
        '''

        self.pool_=await aiomysql.create_pool(host=HOST,port=PORT_A,
                                                user=USER,password=PASSWORD,
                                                db=DB,autocommit=False,
                                                loop=self.newlp)
        print("mysql线程池开启完毕~~~")


    async def select_one(self, sql,args=None):
        '''
        查询单个
        '''
        async with self.pool_.acquire() as conn:

            async with conn.cursor(aiomysql.DictCursor) as cur:
                if args==None:

                    await cur.execute(sql)
                else:
                    await cur.execute(sql,args)

                result_info = await cur.fetchone()
        
        return result_info

    async def select_all(self, sql,args= None):
        
        '''
        查询所有

        '''
        async with self.pool_.acquire() as conn:

            async with conn.cursor(aiomysql.DictCursor) as cur:
                if args==None:

                    await cur.execute(sql)
                else:
                    await cur.execute(sql,args)

                result_info = await cur.fetchall()
        
        return result_info


    async def update_info(self, sql,args=None):
    
        '''
        更新信息

        '''
        async with self.pool_.acquire() as conn:

            async with conn.cursor(aiomysql.DictCursor) as cur:
                if args==None:

                    await cur.execute(sql)
                else:
                    await cur.execute(sql,args)

                await conn.commit()
        
        return {'message': 'ok'}


    async def update_info(self, sql,args=None):
    
        '''
        删除信息

        '''
        async with self.pool_.acquire() as conn:

            async with conn.cursor(aiomysql.DictCursor) as cur:
                if args==None:

                    await cur.execute(sql)
                else:
                    await cur.execute(sql,args)

                await conn.commit()
        
        return {'message': 'ok'}

    
    async def insert_info(self, sql,args=None):
    
        '''
        插入信息

        '''
        async with self.pool_.acquire() as conn:

            async with conn.cursor(aiomysql.DictCursor) as cur:

                if args==None:

                    await cur.execute(sql)
                    
                else:

                    await cur.execute(sql,args)

                await conn.commit()

        return {'message': 'ok'}


# x=ConnMysql()
# # sql='select * from dirver where dirver_name=%s'
# sql_one='insert into dirver(dirver_name,dirver_phone) values(%s,%s)'
# # # sql_two='insert into dirver(dirver_name,dirver_phone) values(%s,%s)'

# # s=asyncio.run_coroutine_threadsafe(x.select_all(sql,('小李')), x.newlp)
# s=asyncio.run_coroutine_threadsafe(x.insert_info(sql_one,('小李','132423494')), x.newlp)
# print(s)
# print(s.result())
                


